<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use Cache,Event,Auth;

class QyphController extends Controller
{
	 public function index(){
	 	//获取登录的企业
	 	$userid['id'] = Auth::guard('admin')->user()->unit_id;
	 	
	 	//获取属于自己地区的企业
	 	$a = DB::table('units')->where($userid)->select('city','area')->first();
	 	$b['city'] = $a->city;
	 	$b['area'] = $a->area;
	 	$c = DB::table('units')->where($b)->get();

	 	foreach ($c as $key => $value) {
	 		$d['id'] = $value->city;
	 		$c[$key]->city = DB::table('area')->where($d)->select('name')->first();

	 		$e['id'] = $value->area;
	 		$c[$key]->area = DB::table('area')->where($e)->select('name')->first();

	 		$f['id'] = $value->org_id;
	 		$org = DB::table('area')->where($f)->select('name')->first();
	 	
	 		if ($org) {
 				$c[$key]->org_id = $org->name;
	 		}else{
	 			$c[$key]->org_id = '暂无';
	 		}

 	 	}	
 	 	
        return view('admin.qyph.index',['list'=>$c]);

    }


    public function leida($id,Request $request){

    	//获取警情上报数
    	$jq = DB::table('jqsb')->where('unit_id',$id)->count();
    	if (!$jq) {
    		$jq = '0';
    	}
    	//获取人员核查
    	$rysh = DB::table('rysh')->where('unit_id',$id)->count();
    	if (!$rysh) {
    		$rysh = '0';
    	}


    	//获取线索举报
    	$xs = DB::table('xiansuo')->where('unit_id',$id)->count();
		if (!$xs) {
    		$xs = '0';
    	}


    	//获取意见反馈
    	$yj = DB::table('yjfk')->where('unit_id',$id)->count();
    	if (!$yj) {
    		$yj = '0';
    	}

    	//获取企业信息完整度
    	//法人
    	$one = DB::table('faren')->where('unit_id',$id)->first();
    	if ($one) {
    		$on = '20';
    	}else{
    		$on = '0';
    	}

    	//保卫人员
    	$two = DB::table('baoweirenyuan')->where('unit_id',$id)->first();
    	if ($two) {
    		$tw = '20';
    	}else{
    		$tw = '0';
    	}

    	//重点岗位人员
    	$three = DB::table('zhongdianrenyuan')->where('unit_id',$id)->first();
    	if ($three) {
    		$thr = '20';
    	}else{
    		$thr = '0';
    	}

    	//重要部位
    	$four = DB::table('zhongyaobuwei')->where('unit_id',$id)->first();
    	if ($four) {
    		$fo = '20';
    	}else{
    		$fo = '0';
    	}
    	//基本信息
    	$five = DB::table('zhongyaobuwei')->where('id',$id)->first();
    	if (!$five) {
    		$a = '0'; 
    		$b = '0';  
    		$c = '0';	
    		$d = '0';	
    	}else{

	    	if ($five->name == '') {
	    		$a = '0';
	    	}else{
	    		$a = '5';
	    	}

	    	if ($five->city == '') {
	    		$b = '0';
	    	}else{
	    		$b = '5';
	    	}

	    	if ($five->area == '') {
	    		$c = '0';
	    	}else{
	    		$c = '5';
	    	}

	    	if ($five->org_id == '') {
	    		$d = '0';
	    	}else{
	    		$d = '5';
	    	}
    	

    	}






    	$score = $on+$tw+$thr+$fo+$a+$b+$c+$d;


    	return view('admin.qyph.leida',['jq'=>$jq,'rysh'=>$rysh,'xs'=>$xs,'yj'=>$yj,'score'=>$score]);
    }


    public function getnew(Request $request){

	    	$userid['id'] = Auth::guard('admin')->user()->unit_id;
		 	$a = DB::table('units')->where($userid)->first();

    	 
    	   	
    	   	 	//默认所内,本周内,人员审核,人员核查
    	   	 	if ( $request->btn == 'ryhc') {
    	   	 		
	    	   	 	if ($request->time == 'zhou') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(rysh.sbsj))';
	                }else if ($request->time == 'yue'){
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(rysh.sbsj))';
	                }else if ($request->time == 'nian') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(rysh.sbsj))';
	                }


	                if ($request->city == 'suo') {
	                    $area =  $a->org_id;
	                    $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	                }elseif ($request->city == 'qu') {
	                    $area =  $a->area;
	                    $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shi'){
	                    $area =  $a->city;
	                    $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shen'){
	                	$ar = '';
	                }


	                // SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.unit_id THEN @rownum WHEN @rowtotal := u.unit_id THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT rysh.unit_id, un.name ,count(1)as count FROM `rysh`  LEFT JOIN units as un ON un.id = rysh.unit_id  WHERE  $ar $nt  GROUP BY rysh.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r

	              	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT rysh.unit_id, un.name ,count(1)as count FROM `rysh`  LEFT JOIN units as un ON un.id = rysh.unit_id  WHERE  $ar $nt  GROUP BY rysh.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
    	   	 	}



    	   	 	//默认所内,本周内,人员审核,警情上报
    	   	 	if ( $request->btn == 'jqsb') {
    	   	 		
	    	   	 	if ($request->time == 'zhou') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jqsb.sbsj))';
	                }else if ($request->time == 'yue'){
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jqsb.sbsj))';
	                }else if ($request->time == 'nian') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jqsb.sbsj))';
	                }


	                if ($request->city == 'suo') {
	                    $area =  $a->org_id;
	                    $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	                }elseif ($request->city == 'qu') {
	                    $area =  $a->area;
	                    $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shi'){
	                    $area =  $a->city;
	                    $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shen'){
	                	$ar = '';
	                }


	                


	              	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT jqsb.unit_id, un.name ,count(1)as count FROM `jqsb`  LEFT JOIN units as un ON un.id = jqsb.unit_id  WHERE  $ar $nt  GROUP BY jqsb.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
    	   	 	}


    	   	 	//默认所内,本周内,人员审核,线索举报
    	   	 	if ( $request->btn == 'xsjb') {
    	   	 		
	    	   	 	if ($request->time == 'zhou') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(xiansuo.jbsj))';
	                }else if ($request->time == 'yue'){
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(xiansuo.jbsj))';
	                }else if ($request->time == 'nian') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(xiansuo.jbsj))';
	                }


	                if ($request->city == 'suo') {
	                    $area =  $a->org_id;
	                    $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	                }elseif ($request->city == 'qu') {
	                    $area =  $a->area;
	                    $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shi'){
	                    $area =  $a->city;
	                    $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shen'){
	                	$ar = '';
	                }


                 



	              	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT xiansuo.unit_id, un.name ,count(1)as count FROM `xiansuo`  LEFT JOIN units as un ON un.id = xiansuo.unit_id  WHERE  $ar $nt  GROUP BY xiansuo.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
    	   	 	}


    	   	 	//默认所内,本周内,人员审核,线索举报
    	   	 	if ( $request->btn == 'yjfk') {
    	   	 		
	    	   	 	if ($request->time == 'zhou') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(yjfk.jbsj))';
	                }else if ($request->time == 'yue'){
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(yjfk.jbsj))';
	                }else if ($request->time == 'nian') {
	                    $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(yjfk.jbsj))';
	                }


	                if ($request->city == 'suo') {
	                    $area =  $a->org_id;
	                    $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	                }elseif ($request->city == 'qu') {
	                    $area =  $a->area;
	                    $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shi'){
	                    $area =  $a->city;
	                    $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	                }elseif($request->city == 'shen'){
	                	$ar = '';
	                }


	                  // SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.unit_id THEN @rownum WHEN @rowtotal := u.unit_id THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT yjfk.unit_id, un.name ,count(1)as count FROM `yjfk`  LEFT JOIN units as un ON un.id = yjfk.unit_id  WHERE  $ar $nt  GROUP BY yjfk.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r



	              	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT yjfk.unit_id, un.name ,count(1)as count FROM `yjfk`  LEFT JOIN units as un ON un.id = yjfk.unit_id  WHERE  $ar $nt  GROUP BY yjfk.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
    	   	 	}










    	   	 	if($re){
	                $result = array('status' => '1000',  'message' => $re);
	                echo json_encode($result,TRUE);
	                exit();
	            }else{
	                $result = array('status' => '1001',  'message' => '暂无数据');
	                echo json_encode($result,TRUE);
	                exit();
	            }



    	   	 	





    	   
    }

}